ExpirationControl

V8 Message Definiton

METADATA

Attribute	Value
Topic	1630-client-book-risk
MLink Token	SystemData
Product	SRControl
accessType	SELECT,UPDATE,INSERT,DELETE

Table Definition

Field	Type	Key	Default Value	Comment
accnt	VARCHAR(16)	PRI	''
year	SMALLINT	PRI	0
month	TINYINT UNSIGNED	PRI	0
day	TINYINT UNSIGNED	PRI	0
clientFirm	VARCHAR(16)	PRI	''	SR client firm
expStatus	enum - OptStatus		'Hold'	expiration trading control
minTheta	FLOAT		-1000	xRM minimum net expiry theta
maxTheta	FLOAT		+1000	xRM maximum net expiry theta
minWtVega	FLOAT		-1000	xRM minimum net expiry wtVegawtVega ve vol sqrtmax01 years 4
maxWtVega	FLOAT		+1000	xRM maximum net expiry wtVegawtVega ve vol sqrtmax01 years 4
maxWtVePctDd	FLOAT		100	of minmax expiry wtVega in DD xde 030 strikes
maxWtVePctDn	FLOAT		100	of minmax expiry wtVega in DN 030 xde 010 strikes
maxWtVePctAt	FLOAT		100	of minmax expiry wtVega in AT 010 xde 010 strikes
maxWtVePctUp	FLOAT		100	of minmax expiry wtVega in UP 010 xde 030 strikes
maxWtVePctDu	FLOAT		100	of minmax expiry wtVega in DU 030 xde strikes
modifiedBy	VARCHAR(24)		''	user who last modified this record
modifiedIn	enum - SysEnvironment		'None'
timestamp	DATETIME(6)		'1900-01-01 00:00:00.000000'	timestamp of last modification

PRIMARY KEY DEFINITION (Unique)

Field	Sequence
accnt	1
year	2
month	3
day	4
clientFirm	5

CREATE TABLE EXAMPLE QUERY

CREATE TABLE `SRControl`.`MsgExpirationControl` (
    `accnt` VARCHAR(16) NOT NULL DEFAULT '',
    `year` SMALLINT NOT NULL DEFAULT 0,
    `month` TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `day` TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `clientFirm` VARCHAR(16) NOT NULL DEFAULT '' COMMENT 'SR client firm',
    `expStatus` ENUM('Hold','TwoWay','BuyOnly','SellOnly','CloseOnly','CloseNow','CloseRisk','BuyCloseOnly','SellCloseOnly') NOT NULL DEFAULT 'Hold' COMMENT 'expiration trading control',
    `minTheta` FLOAT NOT NULL DEFAULT -1000 COMMENT '(xRM) minimum net expiry theta',
    `maxTheta` FLOAT NOT NULL DEFAULT +1000 COMMENT '(xRM) maximum net expiry theta',
    `minWtVega` FLOAT NOT NULL DEFAULT -1000 COMMENT '(xRM) minimum net expiry wtVega;wtVega = ve * vol / sqrt(max(0.1, years * 4))',
    `maxWtVega` FLOAT NOT NULL DEFAULT +1000 COMMENT '(xRM) maximum net expiry wtVega;wtVega = ve * vol / sqrt(max(0.1, years * 4))',
    `maxWtVePctDd` FLOAT NOT NULL DEFAULT 100 COMMENT '% of min/max expiry wtVega in DD {xde < -0.30} strikes',
    `maxWtVePctDn` FLOAT NOT NULL DEFAULT 100 COMMENT '% of min/max expiry wtVega in DN {-0.30 <= xde < -0.10} strikes',
    `maxWtVePctAt` FLOAT NOT NULL DEFAULT 100 COMMENT '% of min/max expiry wtVega in AT {-0.10 <= xde <= +0.10} strikes',
    `maxWtVePctUp` FLOAT NOT NULL DEFAULT 100 COMMENT '% of min/max expiry wtVega in UP {+0.10 < xde <= +0.30} strikes',
    `maxWtVePctDu` FLOAT NOT NULL DEFAULT 100 COMMENT '% of min/max expiry wtVega in DU {+0.30 < xde} strikes',
    `modifiedBy` VARCHAR(24) NOT NULL DEFAULT '' COMMENT 'user who last modified this record',
    `modifiedIn` ENUM('None','Neptune','Pluto','V7_Stable','V7_Latest','Saturn','Venus','Mars','SysTest','V7_Current') NOT NULL DEFAULT 'None',
    `timestamp` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000' COMMENT 'timestamp of last modification',
    PRIMARY KEY USING HASH (`accnt`,`year`,`month`,`day`,`clientFirm`)
) ENGINE=SRSE DEFAULT CHARSET=LATIN1 COMMENT='';

SELECT TABLE EXAMPLE QUERY

SELECT
    `accnt`,
    `year`,
    `month`,
    `day`,
    `clientFirm`,
    `expStatus`,
    `minTheta`,
    `maxTheta`,
    `minWtVega`,
    `maxWtVega`,
    `maxWtVePctDd`,
    `maxWtVePctDn`,
    `maxWtVePctAt`,
    `maxWtVePctUp`,
    `maxWtVePctDu`,
    `timestamp`
FROM `SRControl`.`MsgExpirationControl`
WHERE 
    /* Replace with a VARCHAR(16) */ 
    `accnt` = 'Example_accnt'
  AND
    /* Replace with a SMALLINT */ 
    `year` = 0
  AND
    /* Replace with a TINYINT UNSIGNED */ 
    `month` = 1
  AND
    /* Replace with a TINYINT UNSIGNED */ 
    `day` = 1
  AND
    /* Replace with a VARCHAR(16) */ 
    `clientFirm` = 'Example_clientFirm';

UPDATE TABLE EXAMPLE QUERY

UPDATE `SRControl`.`MsgExpirationControl` 
SET
    /* Replace with a ENUM('Hold','TwoWay','BuyOnly','SellOnly','CloseOnly','CloseNow','CloseRisk','BuyCloseOnly','SellCloseOnly') */
    `expStatus` = 'Hold',
    /* Replace with a FLOAT */
    `minTheta` = 1.23,
    /* Replace with a FLOAT */
    `maxTheta` = 1.23,
    /* Replace with a FLOAT */
    `minWtVega` = 1.23,
    /* Replace with a FLOAT */
    `maxWtVega` = 1.23,
    /* Replace with a FLOAT */
    `maxWtVePctDd` = 1.23,
    /* Replace with a FLOAT */
    `maxWtVePctDn` = 1.23,
    /* Replace with a FLOAT */
    `maxWtVePctAt` = 1.23,
    /* Replace with a FLOAT */
    `maxWtVePctUp` = 1.23,
    /* Replace with a FLOAT */
    `maxWtVePctDu` = 1.23,
    /* Replace with a DATETIME(6) */
    `timestamp` = '2022-01-01 12:34:56.000000'
WHERE
    /* Replace with a VARCHAR(16) */ 
    `accnt` = 'Example_accnt'
  AND
    /* Replace with a SMALLINT */ 
    `year` = 0
  AND
    /* Replace with a TINYINT UNSIGNED */ 
    `month` = 1
  AND
    /* Replace with a TINYINT UNSIGNED */ 
    `day` = 1
  AND
    /* Replace with a VARCHAR(16) */ 
    `clientFirm` = 'Example_clientFirm';

INSERT TABLE EXAMPLE QUERY

INSERT INTO `SRControl`.`MsgExpirationControl`(
    /* Replace with a VARCHAR(16) */ 
    `accnt`,
    /* Replace with a SMALLINT */ 
    `year`,
    /* Replace with a TINYINT UNSIGNED */ 
    `month`,
    /* Replace with a TINYINT UNSIGNED */ 
    `day`,
    /* Replace with a VARCHAR(16) */ 
    `clientFirm`,
    /* Replace with a ENUM('Hold','TwoWay','BuyOnly','SellOnly','CloseOnly','CloseNow','CloseRisk','BuyCloseOnly','SellCloseOnly') */
    `expStatus`,
    /* Replace with a FLOAT */
    `minTheta`,
    /* Replace with a FLOAT */
    `maxTheta`,
    /* Replace with a FLOAT */
    `minWtVega`,
    /* Replace with a FLOAT */
    `maxWtVega`,
    /* Replace with a FLOAT */
    `maxWtVePctDd`,
    /* Replace with a FLOAT */
    `maxWtVePctDn`,
    /* Replace with a FLOAT */
    `maxWtVePctAt`,
    /* Replace with a FLOAT */
    `maxWtVePctUp`,
    /* Replace with a FLOAT */
    `maxWtVePctDu`,
    /* Replace with a DATETIME(6) */
    `timestamp`
) 
VALUES(
    'Example_accnt',
    0,
    1,
    1,
    'Example_clientFirm',
    'Hold',
    1.23,
    1.23,
    1.23,
    1.23,
    1.23,
    1.23,
    1.23,
    1.23,
    1.23,
    '2022-01-01 12:34:56.000000'
);

DELETE TABLE EXAMPLE QUERY

DELETE FROM `SRControl`.`MsgExpirationControl` 
WHERE
    /* Replace with a VARCHAR(16) */ 
    `accnt` = 'Example_accnt'
  AND
    /* Replace with a SMALLINT */ 
    `year` = 0
  AND
    /* Replace with a TINYINT UNSIGNED */ 
    `month` = 1
  AND
    /* Replace with a TINYINT UNSIGNED */ 
    `day` = 1
  AND
    /* Replace with a VARCHAR(16) */ 
    `clientFirm` = 'Example_clientFirm';

Doc Columns Query

SELECT * FROM SRControl.doccolumns WHERE TABLE_NAME='ExpirationControl' ORDER BY ordinal_position ASC;